package lv.solcraft.replication.client;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import lv.solcraft.replication.client.logging.ClientLogger;
import lv.solcraft.replication.common.OperDTO;
import lv.solcraft.replication.common.OperDTO.Field;

import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.core.RowCallbackHandler;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.jdbc.object.BatchSqlUpdate;

public class OperLoaderImpl extends JdbcDaoSupport implements OperLoader {

    private static final String GET_OPERS_SQL = "SELECT OPER_ID, OPER_TYPE, TABLE_NAME, "
            + "SCR_SITE_ID, SCR_RECORD_ID, OPER_DATETIME " + "FROM SCR_OPERS ORDER BY OPER_ID";

    private static final String GET_UPDATED_FIELD_NAMES_SQL = "SELECT FIELD_NAME "
            + "FROM SCR_OPER_FIELDS WHERE OPER_ID = ?";

    private static final String GET_DESCRIPTION_FIELDS_SQL = "SELECT FIELD_NAME, FIELD_VALUE "
            + "FROM SCR_OPER_DESCR_FIELDS WHERE OPER_ID = ?";

    private static final int JDBC_FETCH_SIZE = 50;

    private ClientConfig client;

    private ClientLogger clientLogger;

    private OperLoaderSqlHolder sqlHolder;

    private BatchSqlUpdate deleteOperBatch;

    private BatchSqlUpdate deleteFieldBatch;

    private BatchSqlUpdate deleteDescrFieldBatch;

    public OperLoaderSqlHolder getSqlHolder() {
        return sqlHolder;
    }

    public void setSqlHolder(OperLoaderSqlHolder sqlHolder) {
        this.sqlHolder = sqlHolder;
    }

    @SuppressWarnings("unchecked")
    public List<OperDTO> loadOpers(final int maxOpers) {
        clientLogger.debugLocal("Finding new opers standard oper order");
        JdbcTemplate jt = new JdbcTemplate(getDataSource());
        jt.setFetchSize(JDBC_FETCH_SIZE);

        List<OperDTO> opers = (List) jt.query(GET_OPERS_SQL, new Object[] {}, new ResultSetExtractor() {
            public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
                List<OperDTO> result = new ArrayList<OperDTO>(maxOpers);
                int ignoredOpersCount = 0;
                while (result.size() < maxOpers && rs.next()) {
                    OperDTO oper = new OperDTO();
                    oper.setOperId(rs.getInt(1));
                    oper.setType(rs.getString(2));
                    oper.setTable(rs.getString(3).trim());
                    oper.setRecordSiteId(rs.getInt(4));
                    oper.setRecordId(rs.getInt(5));
                    oper.setDateTime(new Date(rs.getTimestamp(6).getTime()));
                    oper.setOperOwnerSiteId(client.getSiteId());

                    if (oper.isTypeInsert()) {
                        loadInsertOperFields(oper);
                    } else if (oper.isTypeUpdate()) {
                        loadUpdateOperFields(oper);
                        loadBlobFields(oper);
                    }
                    loadDescrFields(oper);

                    // if it is insert or update oper and no fields
                    // there, then ignore it
                    if ((oper.isTypeInsert() || oper.isTypeUpdate()) && oper.getFields().size() == 0) {
                        clientLogger.debugLocal("Ignoring (deleting) insert/update oper without fields, oper id "
                                + oper.getOperId());
                        delete(oper);
                        ignoredOpersCount++;
                    } else {
                        result.add(oper);
                    }
                }
                if (ignoredOpersCount > 0) {
                    clientLogger.infoLocal("Ignored and removed " + ignoredOpersCount
                            + " insert/update opers without fields");
                }
                return result;
            }
        });

        return opers;
    }

    protected void loadBlobFields(OperDTO oper) {
        for (Field field : oper.getFields()) {
            for (String blobField : client.getSettings().getBlobFields(oper.getTable(), field.getName())) {
                loadBlobField(oper, blobField);
            }
        }
    }

    private void loadBlobField(final OperDTO oper, final String blobField) {
        getJdbcTemplate().query(
                "SELECT " + blobField + " FROM " + oper.getTable() + " WHERE SCR_SITE_ID = ? AND SCR_RECORD_ID = ?",
                new Object[] { oper.getRecordSiteId(), oper.getRecordId() }, new RowCallbackHandler() {
                    public void processRow(ResultSet rs) throws SQLException {
                        byte[] bytes = rs.getBytes(1);
                        oper.getBlobFields().put(blobField, bytes);
                    }
                });
    }

    public ClientConfig getClient() {
        return client;
    }

    public void setClient(ClientConfig client) {
        this.client = client;
    }

    public ClientLogger getClientLogger() {
        return clientLogger;
    }

    public void setClientLogger(ClientLogger clientLogger) {
        this.clientLogger = clientLogger;
    }

    private void loadInsertOperFields(final OperDTO oper) {
        final OperLoaderSqlHolder.LoadFieldsStatement statement = sqlHolder.getLoadFieldsStatement(oper.getTable());
        getJdbcTemplate().query(statement.getSql(), new Object[] { oper.getRecordSiteId(), oper.getRecordId() },
                new ResultSetExtractor() {
                    public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
                        if (!rs.next()) {
                            // probably operation is already deleted, skip field
                            // loading
                            return null;
                        }
                        for (int i = 0; i < statement.getFields().size(); i++) {
                            String fieldName = statement.getFields().get(i);
                            if (!(fieldName.equals("SCR_SITE_ID")) && !(fieldName.equals("SCR_RECORD_ID"))) {
                                String fieldValue = rs.getString(i + 1);
                                if (fieldValue != null) {
                                    oper.addField(fieldName, fieldValue);
                                }
                            }
                        }
                        return null;
                    }
                });
    }

    private void loadUpdateOperFields(final OperDTO oper) {
        final List updatedFields = (List) getJdbcTemplate().query(GET_UPDATED_FIELD_NAMES_SQL,
                new Object[] { oper.getOperId() }, new ResultSetExtractor() {
                    public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
                        List fields = new ArrayList();
                        while (rs.next()) {
                            String field = rs.getString(1).trim().toUpperCase();
                            fields.add(field);
                        }
                        return fields;
                    }

                });

        final OperLoaderSqlHolder.LoadFieldsStatement statement = sqlHolder.getLoadFieldsStatement(oper.getTable());
        getJdbcTemplate().query(statement.getSql(), new Object[] { oper.getRecordSiteId(), oper.getRecordId() },
                new ResultSetExtractor() {

                    public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
                        if (!rs.next()) {
                            // probably operation is already deleted, skip field
                            // loading
                            return null;
                        }

                        for (int i = 0; i < statement.getFields().size(); i++) {
                            String fieldName = statement.getFields().get(i);
                            if (!(fieldName.equals("SCR_SITE_ID")) && !(fieldName.equals("SCR_RECORD_ID"))) {
                                if (updatedFields.contains(fieldName)) {
                                    String fieldValue = rs.getString(i + 1);
                                    oper.addField(fieldName, fieldValue);
                                }
                            }
                        }
                        return null;
                    }
                });
    }

    private void loadDescrFields(final OperDTO oper) {
        getJdbcTemplate().query(GET_DESCRIPTION_FIELDS_SQL, new Object[] { oper.getOperId() },
                new ResultSetExtractor() {

                    public Object extractData(ResultSet rs) throws SQLException, DataAccessException {
                        while (rs.next()) {
                            String name = rs.getString(1).trim().toUpperCase();
                            String value = rs.getString(2);
                            oper.addDescrField(name, value);
                        }
                        return null;
                    }

                });
    }

    public void delete(OperDTO oper) {
        List<OperDTO> opers = new ArrayList<OperDTO>(1);
        opers.add(oper);
        delete(opers);
    }

    public void delete(List<OperDTO> opers) {

        deleteDescrFieldBatch.reset();
        deleteFieldBatch.reset();
        deleteOperBatch.reset();

        for (OperDTO oper : opers) {
            deleteDescrFieldBatch.update(oper.getOperId());
            deleteFieldBatch.update(oper.getOperId());
            deleteOperBatch.update(oper.getOperId());
        }

        deleteDescrFieldBatch.flush();
        deleteFieldBatch.flush();
        deleteOperBatch.flush();
    }

    private void compileOperDeleteBatch() {
        deleteOperBatch = new BatchSqlUpdate(getDataSource(), "DELETE FROM SCR_OPERS WHERE OPER_ID = ?");
        deleteOperBatch.declareParameter(new SqlParameter(Types.INTEGER));
        deleteOperBatch.compile();
    }

    private void compileFieldDeleteBatch() {
        deleteFieldBatch = new BatchSqlUpdate(getDataSource(), "DELETE FROM SCR_OPER_FIELDS WHERE OPER_ID = ?");
        deleteFieldBatch.declareParameter(new SqlParameter(Types.INTEGER));
        deleteFieldBatch.compile();
    }

    private void compileDescrFieldDeleteBatch() {
        deleteDescrFieldBatch = new BatchSqlUpdate(getDataSource(),
                "DELETE FROM SCR_OPER_DESCR_FIELDS WHERE OPER_ID = ?");
        deleteDescrFieldBatch.declareParameter(new SqlParameter(Types.INTEGER));
        deleteDescrFieldBatch.compile();
    }

    public void init() {
        compileOperDeleteBatch();
        compileFieldDeleteBatch();
        compileDescrFieldDeleteBatch();
    }

}
